using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

// 引入office相关的COM组件
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;

namespace fangxiu
{
    public partial class Form1 : Form
    {  
        //工单	型号	工单量	领料	复判	撕胶	喷码	换卡扣	解焊	贴膜	焊锡	模组测试	
        //分析	拔片	LCD清洁	贴片	刮胶清洗	取IC/FPC	压IC/FPC	FOG测试	涂胶	FOG外观	入骨架	出货	
        //    二次功能	二次返片	B规	包装	破屏	LCD报废

        //工单-型号列表
        private static Dictionary<String, String> GongDan_XingHao = null;
        //工单-工单量列表
        private static Dictionary<String, String> GongDan_GongDanLiang = null;

        //工单-复判列表
        private static Dictionary<String, int> GongDan_FuPan = null;
        //工单-撕胶列表
        private static Dictionary<String, int> GongDan_ShiJiao = null;
        //工单-解焊列表
        private static Dictionary<String, int> GongDan_JieHang = null;
        //工单-修卡扣列表
        private static Dictionary<String, int> GongDan_XiuKaKou = null;
        //工单-贴膜列表
        private static Dictionary<String, int> GongDan_TieMo = null;
        //工单-分析列表
        private static Dictionary<String, int> GongDan_FengXi = null;
        //工单-拨片列表
        private static Dictionary<String, int> GongDan_BoPian = null;
        //工单-LCD清洁列表
        private static Dictionary<String, int> GongDan_LCD_QingJIE = null;
        //工单-贴片列表
        private static Dictionary<String, int> GongDan_TiePian = null;
        //工单-刮胶清洁列表
        private static Dictionary<String, int> GongDan_GuaJiaoQingJie = null;
        //工单-拆IC排线列表
        private static Dictionary<String, int> GongDan_Cai_IC_PaiXian = null;
        //工单-压IC排线列表
        private static Dictionary<String, int> GongDan_Ya_IC_PaiXian = null;
        //工单-FOG测试列表
        private static Dictionary<String, int> GongDan_FOG_Cheshi = null;
        //工单-FOG外观列表
        private static Dictionary<String, int> GongDan_FOG_WaiGuang = null;

        //文件路径
        private String ExcelFilePath = null;



        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            //程序启动,初始化每个列表
            GongDan_XingHao = new Dictionary<String, String>();
            GongDan_GongDanLiang = new Dictionary<String, String>();
            GongDan_FuPan = new Dictionary<String, int>();
            GongDan_ShiJiao = new Dictionary<String, int>();
            GongDan_JieHang = new Dictionary<String, int>();
            GongDan_XiuKaKou = new Dictionary<String, int>();
            GongDan_TieMo = new Dictionary<String, int>();
            GongDan_FengXi = new Dictionary<String, int>();
            GongDan_BoPian = new Dictionary<String, int>();
            GongDan_LCD_QingJIE = new Dictionary<String, int>();
            GongDan_TiePian = new Dictionary<String, int>();
            GongDan_GuaJiaoQingJie = new Dictionary<String, int>();
            GongDan_Cai_IC_PaiXian = new Dictionary<String, int>();
            GongDan_Ya_IC_PaiXian = new Dictionary<String, int>();
            GongDan_FOG_Cheshi = new Dictionary<String, int>();
            GongDan_FOG_WaiGuang = new Dictionary<String, int>(); 
        }

        //打开按钮
        private void button1_Click(object sender, EventArgs e)
        {
            //获取文件路径
            ExcelFilePath = GetOpenExcelFilePath();
            ShowLog("已选择文件:" + ExcelFilePath);
        }

        //分析按钮
        private void button2_Click(object sender, EventArgs e)
        {
            //判断文件路径
            if (ExcelFilePath == null)
            {
                ShowLog("打开文件失败");
                return;
            }

            //读取工单-工单量列表
            ReadExcel(ExcelFilePath);

            //数字为sheet的顺序
            //读取复判
            ReadExcelWithSheetNumber(ExcelFilePath, 2, GongDan_FuPan);
            //读取撕胶
            ReadExcelWithSheetNumber(ExcelFilePath, 3, GongDan_ShiJiao);
            //读取解焊
            ReadExcelWithSheetNumber(ExcelFilePath, 4, GongDan_JieHang);
            //读取修卡扣
            ReadExcelWithSheetNumber(ExcelFilePath, 5, GongDan_XiuKaKou);
            //读取贴膜
            ReadExcelWithSheetNumber(ExcelFilePath, 6, GongDan_TieMo);
            //读取分析
            ReadExcelWithSheetNumber(ExcelFilePath, 7, GongDan_FengXi);
            //读取拨片
            ReadExcelWithSheetNumber(ExcelFilePath, 8, GongDan_BoPian);
            //读取LCD清洁
            ReadExcelWithSheetNumber(ExcelFilePath, 9, GongDan_LCD_QingJIE);
            //读取贴片
            ReadExcelWithSheetNumber(ExcelFilePath, 10, GongDan_TiePian);
            //读取刮胶清洁
            ReadExcelWithSheetNumber(ExcelFilePath, 11, GongDan_GuaJiaoQingJie);
            //读取拆IC排线
            ReadExcelWithSheetNumber(ExcelFilePath, 12, GongDan_Cai_IC_PaiXian);

            //读取压IC排线
            ReadExcelWithSheetNumber(ExcelFilePath, 13, GongDan_Ya_IC_PaiXian);
            //读取-FOG测试
            ReadExcelWithSheetNumber(ExcelFilePath, 14, GongDan_FOG_Cheshi);
            //读取FOG外观
            ReadExcelWithSheetNumber(ExcelFilePath, 15, GongDan_FOG_WaiGuang);
 
            ShowLog("添加零设置");
            //sheet内所有不存在的工单添加, 数量设置0
            foreach (KeyValuePair<string, string> kvp in GongDan_XingHao)
            {
                AddEmptyGongDan(kvp.Key,  GongDan_FuPan);
            //设置撕胶
                AddEmptyGongDan(kvp.Key, GongDan_ShiJiao);
            //设置解焊
                AddEmptyGongDan(kvp.Key, GongDan_JieHang);
            //设置修卡扣
                AddEmptyGongDan(kvp.Key, GongDan_XiuKaKou);
            //设置贴膜
                AddEmptyGongDan(kvp.Key, GongDan_TieMo);
            //设置分析
                AddEmptyGongDan(kvp.Key, GongDan_FengXi);
            //设置拨片
                AddEmptyGongDan(kvp.Key, GongDan_BoPian);
            //设置LCD清洁
                AddEmptyGongDan(kvp.Key, GongDan_LCD_QingJIE);
            //设置贴片
                AddEmptyGongDan(kvp.Key, GongDan_TiePian);
            //设置刮胶清洁
                AddEmptyGongDan(kvp.Key, GongDan_GuaJiaoQingJie);
            //设置拆IC排线
                AddEmptyGongDan(kvp.Key, GongDan_Cai_IC_PaiXian);

            //设置压IC排线
                AddEmptyGongDan(kvp.Key, GongDan_Ya_IC_PaiXian);
            //设置-FOG测试
                AddEmptyGongDan(kvp.Key, GongDan_FOG_Cheshi);
            //设置FOG外观
                AddEmptyGongDan(kvp.Key, GongDan_FOG_WaiGuang);
            
            }
            ShowLog("读取完成");
        }

        //保存按钮
        private void button3_Click(object sender, EventArgs e)
        {
            WriteExcel(GetSaveExcelFilePath());
            ShowLog("保存完成");
        }


        //显示信息到窗口
        public void ShowLog(String log)
        {
            textBox1.AppendText("\r\n" + log);
        }


        //选择文件
        public String GetOpenExcelFilePath()
        {
            String FilePath = null;
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Title = "请选择文件";

            ////获取应用程序的当前工作目录
            openFileDialog1.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
            openFileDialog1.Filter = "Excel文件|*.xls;*.xlsx";
            /*
             * FilterIndex 属性用于选择了何种文件类型,缺省设置为0,系统取Filter属性设置第一项
             * ,相当于FilterIndex 属性设置为1.如果你编了3个文件类型，当FilterIndex ＝2时是指第2个.
             */
            openFileDialog1.FilterIndex = 2;
            /*
             *如果值为false，那么下一次选择文件的初始目录是上一次你选择的那个目录，
             *不固定；如果值为true，每次打开这个对话框初始目录不随你的选择而改变，是固定的  
             */
            openFileDialog1.RestoreDirectory = false;
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                FilePath = System.IO.Path.GetFullPath(openFileDialog1.FileName);
            }

            return FilePath;

        }


        public String GetSaveExcelFilePath()
        {
            string localFilePath = null;
            //string localFilePath, fileNameExt, newFileName, FilePath; 
            SaveFileDialog saveFileDialog = new SaveFileDialog();

            //设置文件类型 
            saveFileDialog.Filter = "Excel文件|*.xlsx;*.xls";

            //设置默认文件类型显示顺序 
            saveFileDialog.FilterIndex = 1;

            //保存对话框是否记忆上次打开的目录 
            saveFileDialog.RestoreDirectory = true;

            //设置默认的文件名(本人测试没成功)
            //saveFileDialog.DefaultFileName = "lucks";// in wpf is  sfd.FileName = "YourFileName";


            //点了保存按钮进入 
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                localFilePath = saveFileDialog.FileName.ToString(); //获得文件路径 
                //string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名，不带路径
                //string filePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));//获取文件路径，不带文件名
                //给文件名前加上时间
                //newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;
                //在文件名里加字符
                //saveFileDialog1.FileName.Insert(1,"dameng");   
                //System.IO.FileStream fs = (System.IO.FileStream)saveFileDialog1.OpenFile();//输出文件
                //fs输出带文字或图片的文件，就看需求了
            }

            return localFilePath;
        }

        //打开读取excel文档的sheet1,,读取工单-工单量列表sheet用, 直接读取一个sheet
        public void ReadExcel(String filepath)
        {

            //打开已有excel文档，或者创建一个新的excel文档 
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            if (app == null)
            {
                MessageBox.Show("可能您的机子未安装excel，无法创建excel对象！", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }



            Workbooks wbks = app.Workbooks;
            Workbook wbk = wbks.Add(filepath);
            //注：若新建一个excel文档，“filepath”替换成true即可；不过这里新建的excel文档默认只有一个sheet。

            //Workbook wbk = wbks.Open(filepath, missing, true, missing, missing, missing,
            // missing, missing, missing, true, missing, missing, missing, missing, missing);



            Sheets shs = wbk.Sheets;//定义一个新的工作表

            //打开第一个Sheet
            _Worksheet wsh = (_Worksheet)shs.get_Item(1);
            ShowLog("开始读取文件表格:" + 1 + ", 表格名称=" + wsh.Name);
            wsh.Activate();//激活工作表


            #region 取得总记录行数(包括标题列)
            ///UsedRange:返回一个Microsoft.Office.Interop.Excel.Range对象，该对象表示指定工作表上使用的范围。 只读。
            ///Cells:返回一个Range对象，它表示指定范围内的单元格。
            ///Rows返回表示指定范围内的行的Microsoft.Office.Interop.Excel.Range对象。
            ///Count:返回集合中的对象数量。
            #endregion

            int rowsInt = wsh.UsedRange.Cells.Rows.Count; //得到非空行数
            int columnsInt = wsh.UsedRange.Cells.Columns.Count;//得到非空列数


            //判断多少行,最多100000
            for (int i = 1; i <= 100000; i++)
            {
                Range range = (Range)wsh.Cells[i, 1];
                String cellText = range.Text.ToString();
                //读取第一列到空就跳出
                if (String.IsNullOrEmpty(cellText) || cellText.Equals("") || cellText.Equals(" "))
                {
                    //这里是不是搞错了
                    columnsInt = i;
                    break;
                }
            }


            //读取工单和型号-工单量
            for (int i = 2; i <= rowsInt; i++)
            { 
                //工单,选取第1列
                Range gongdan = (Range)wsh.Cells[i, 1];
                String gongdanCellText = gongdan.Text.ToString();
                //型号,选取第2列
                Range xinghao = (Range)wsh.Cells[i, 2];
                String xinghaoCellText = xinghao.Text.ToString();
                //工单量,选取第3列
                Range gongdanliang = (Range)wsh.Cells[i, 3];
                String gongdanliangCellText = gongdanliang.Text.ToString();

         

                //添加到工单-型号工单量列表, 去除重复
                if (!GongDan_XingHao.ContainsKey(gongdanCellText))
                {
                    GongDan_XingHao.Add(gongdanCellText, xinghaoCellText);
                    GongDan_GongDanLiang.Add(gongdanCellText, gongdanliangCellText);
                }
                else
                {
                    ShowLog("工单列表有重复-" + gongdanCellText);
                }


            }


            wbk.Close();
            app.Quit();
            return;
        }


        //打开excel文档的指定sheet,读取其他sheet用, SheetIndex为sheet顺序
        public void ReadExcelWithSheetNumber(String filepath, int SheetIndex, Dictionary<String, int> SheetDictionary)
        {

            //打开已有excel文档，或者创建一个新的excel文档 
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            if (app == null)
            {
                MessageBox.Show("可能您的机子未安装excel，无法创建excel对象！", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }



            Workbooks wbks = app.Workbooks;
            Workbook wbk = wbks.Add(filepath);
            //注：若新建一个excel文档，“filepath”替换成true即可；不过这里新建的excel文档默认只有一个sheet。

            //Workbook wbk = wbks.Open(filepath, missing, true, missing, missing, missing,
            // missing, missing, missing, true, missing, missing, missing, missing, missing);



            Sheets shs = wbk.Sheets;//定义一个新的工作表

            //SheetIndex是要取得的sheet的index
            _Worksheet wsh = (_Worksheet)shs.get_Item(SheetIndex);
            ShowLog("开始读取文件表格:" + SheetIndex + ", 表格名称=" + wsh.Name);
            wsh.Activate();//激活工作表


            #region 取得总记录行数(包括标题列)
            ///UsedRange:返回一个Microsoft.Office.Interop.Excel.Range对象，该对象表示指定工作表上使用的范围。 只读。
            ///Cells:返回一个Range对象，它表示指定范围内的单元格。
            ///Rows返回表示指定范围内的行的Microsoft.Office.Interop.Excel.Range对象。
            ///Count:返回集合中的对象数量。
            #endregion

            int rowsInt = wsh.UsedRange.Cells.Rows.Count; //得到非空行数
            int columnsInt = wsh.UsedRange.Cells.Columns.Count;//得到非空列数
            //System.Diagnostics.Debug.Assert(false, "rowsint" + rowsint);
            //System.Diagnostics.Debug.Assert(false, "columnsint" + columnsint); 
 

            //判断多少行
            for (int i = 1; i <= 100000; i++)
            {
                Range range = (Range)wsh.Cells[i, 2];
                String cellText = range.Text.ToString();

                //去除空白
                cellText.Trim();
                if (String.IsNullOrEmpty(cellText) || cellText.Equals("") || cellText.Equals(" "))
                {
                    //这里是不是搞错了
                    columnsInt = i;
                    break;

                }
            }


            //读取工单和型号-工单量
            for (int i = 2; i <= rowsInt; i++)
            {
                //工单,选取第2列
                Range gongdan = (Range)wsh.Cells[i, 2];
                String gongdanCellText = gongdan.Text.ToString();
                //去除空白
                gongdanCellText.Trim();

                //进站数 ,选取第9列
                Range jinzhanshu = (Range)wsh.Cells[i, 9];
                String jinzhanshuCellText = jinzhanshu.Text.ToString();
                jinzhanshuCellText.Trim();
                //出站数 ,选取第10列
                Range chuzhanshu = (Range)wsh.Cells[i, 10];
                String chuzhanshuCellText = chuzhanshu.Text.ToString();
                chuzhanshuCellText.Trim();

                //默认值进站数0
                int jinzhanshuInt = 0;
                if (isNumberic(jinzhanshuCellText))
                {
                    //尝试表格内容转为数字
                    jinzhanshuInt = Convert.ToInt32(jinzhanshuCellText);
                }
                //默认值出站数0
                int chuzhanshuInt = 0;
                if (isNumberic(chuzhanshuCellText))
                {
                    //尝试表格内容转为数字
                    chuzhanshuInt = Convert.ToInt32(chuzhanshuCellText);
                }

                //进站-出站
                int zongshu = jinzhanshuInt - chuzhanshuInt;

                //工单-出站入站列表, 去除重复
                if (!SheetDictionary.ContainsKey(gongdanCellText))
                {
                    //添加到对应词典
                    SheetDictionary.Add(gongdanCellText, zongshu);
                }
                //ShowLog(gongdanCellText + " " + zongshu);
            }


            wbk.Close();
            app.Quit();
            return;
        }

        //结果保存到excel文件
        public void WriteExcel(String filepath)
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workBook = excel.Workbooks.Add();
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Worksheet)workBook.Worksheets.Add();

            //excel.DisplayAlerts = false; // 不显示告警

            int colIndex = 1;
            //工单	型号	工单量	领料	复判	撕胶	喷码	换卡扣	解焊	贴膜	焊锡	模组测试	分析	拔片	LCD清洁	贴片	刮胶清洗	取IC/FPC	压IC/FPC	FOG测试	涂胶	FOG外观	入骨架	出货	二次功能	二次返片	B规	包装	破屏	LCD报废

            String[] title = new String[] { "工单", "型号", "工单量", "复判", "撕胶", "解焊", "修卡扣", "贴膜", "分析", "拔片", "LCD清洁", "贴片", "刮胶清洗", "拆IC排线", "压IC排线", "FOG测试", "FOG外观" };
            ShowLog("保存开始,设置标题");
            //设置标题 
            for (int i = 0; i < title.Length; i++)
            {
                workSheet.Cells[1, colIndex] = title[i];


                colIndex++;
            }
            Range range = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, title.Length]);
            //range.Font.Bold = true;//设置字体
            //range.Font.ColorIndex = 3;//设置字体的颜色
            range.ColumnWidth = 6;//设置单元格的宽度
            range.RowHeight = 30;
            range.Interior.ColorIndex = 50;//设置字体的颜色
            //range.EntireColumn.AutoFit(); //自动调整列宽
            //range.HorizontalAlignment = XlVAlign.xlVAlignCenter; // 文本水平居中方式
            range.Font.Size = 10; //设置字体大小



            //设置表格中的数据 
            //工单-型号列表
            // GongDan_XingHao
            ////工单-工单量列表
            // GongDan_GongDanLiang

            ////工单-复判列表
            // GongDan_FuPan
            ////工单-撕胶列表
            // GongDan_ShiJiao
            ////工单-解焊列表
            // GongDan_JieHang
            ////工单-修卡扣列表
            // GongDan_XiuKaKou
            ////工单-贴膜列表
            // GongDan_TieMo
            ////工单-分析列表
            // GongDan_FengXi
            ////工单-拨片列表
            // GongDan_BoPian
            ////工单-LCD清洁列表
            // GongDan_LCD_QingJIE
            ////工单-贴片列表
            // GongDan_TiePian
            ////工单-刮胶清洁列表
            // GongDan_GuaJiaoQingJie
            ////工单-拆IC排线列表
            // GongDan_Cai_IC_PaiXian
            ////工单-压IC排线列表
            // GongDan_Ya_IC_PaiXian
            ////工单-FOG测试列表
            // GongDan_FOG_Cheshi
            ////工单-FOG外观列表
            // GongDan_FOG_WaiGuang 

            ShowLog("保存文件...");

            //第二行开始写入数据
            int index = 2;

            //开始填充每个工单列表数据, 一行一个工单
            foreach (KeyValuePair<string, string> kvp in GongDan_XingHao)
            { 
               String gongdan = kvp.Key;//获取工单
               String xinghao = kvp.Value;//获取型号
               workSheet.Cells[index, 1] = gongdan;//写入工单
               workSheet.Cells[index, 2] = xinghao;//写入型号

               //读取工单对应的词典数据,写入数据到该行对应的列
               workSheet.Cells[index, 3] = GongDan_GongDanLiang[gongdan];
               workSheet.Cells[index, 4] = GongDan_FuPan[gongdan];
               workSheet.Cells[index, 5] = GongDan_ShiJiao[gongdan];
               workSheet.Cells[index, 6] = GongDan_JieHang[gongdan];
               workSheet.Cells[index, 7] = GongDan_XiuKaKou[gongdan];
               workSheet.Cells[index, 8] = GongDan_TieMo[gongdan];
               workSheet.Cells[index, 9] = GongDan_FengXi[gongdan];
               workSheet.Cells[index, 10] = GongDan_BoPian[gongdan];
               workSheet.Cells[index, 11] = GongDan_LCD_QingJIE[gongdan];
               workSheet.Cells[index, 12] = GongDan_TiePian[gongdan];
               workSheet.Cells[index, 13] = GongDan_GuaJiaoQingJie[gongdan];
               workSheet.Cells[index, 14] = GongDan_Cai_IC_PaiXian[gongdan];
               workSheet.Cells[index, 15] = GongDan_Ya_IC_PaiXian[gongdan];
               workSheet.Cells[index, 16] = GongDan_FOG_Cheshi[gongdan];
               workSheet.Cells[index, 17] = GongDan_FOG_WaiGuang[gongdan];

               //设置下一个工单
                index++;
            }

            

            //文件保存
            ShowLog("保存文件OK");
            workSheet.SaveAs(filepath);
            workBook.Close();
            excel.Quit();


        }



        //所有列表添加没有数据的的工单,设置为0, 防止读取出错, 如果某个流程没有该工单数据,读取会出错,此处填充0
         public void AddEmptyGongDan(String gongdan, Dictionary<String, int> SheetDictionary){
             if(!SheetDictionary.ContainsKey(gongdan)){
                 SheetDictionary.Add(gongdan,0);
             }
         }
         


        static public bool isNumberic(string message)
        {
            //判断是否为整数字符串
            //是的话则将其转换为数字并将其设为out类型的输出值、返回true, 否则为false
            int result = -1;   //result 定义为out 用来输出值
            try
            {
                //当数字字符串的为是少于4时，以下三种都可以转换，任选一种
                //如果位数超过4的话，请选用Convert.ToInt32() 和int.Parse()
                result = Convert.ToInt32(message);
                return true;
            }
            catch
            {
                return false;
            }
        }

        //显示框自动滚动
        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            this.textBox1.SelectionStart = this.textBox1.Text.Length;
            this.textBox1.SelectionLength = 0;
            this.textBox1.ScrollToCaret();
        }

    }
}
